package com.example.dao;

import com.example.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Objects;

public interface UserMapper {

    //登录
    @Select("select * from ftos.user where u_account = #{account} and u_password = #{password}")
    User getUser(@Param("account") String account, @Param("password") String password);
    @Select("select u_password from ftos.user where u_account = #{account}")
    String getHashedPasswordByAccount(String account);
    @Select("select * from ftos.user where u_account = #{u_account}")
    User getUserByAccount(String u_account);

    @Select("select * from ftos.passenger where passenger_ID_card = #{ID_card} and ID_type = #{ID_type}")
    Passenger getPassengerByIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card);
    @Insert("insert into ftos.user(u_name,sex,ID_type,ID_card,u_account,u_password,u_phone,birthday,create_date) values(#{name},#{sex},#{ID_type},#{ID_card},#{account},#{password},#{phone},#{birthday},#{create_date})")
    int addUser(@Param("name") String name, @Param("sex") String sex, @Param("ID_type") String ID_type, @Param("ID_card") String ID_card, @Param("account") String account, @Param("password") String password, @Param("phone") String phone, @Param("birthday") String birthday,@Param("create_date") String create_date);
    @Select("select u_id from ftos.user where ID_card = #{ID_card}")
    int getUidByIDcard(String ID_card);
    @Select("select passenger_id from ftos.user join ftos.passenger on user.ID_card = passenger.passenger_ID_card and user.ID_type = passenger.ID_type where user.u_id = #{u_id}")
    Integer getPidByUid(int u_id);
    @Insert("insert into ftos.bank(u_name,ID_type,u_ID_card) values(#{u_name},#{ID_type},#{u_ID_card})")
    int addBank(@Param("u_name") String u_name,@Param("ID_type") String ID_type, @Param("u_ID_card") String u_ID_card);

    @Select("select count(*) from ftos.user where u_account = #{account}")
    int isExistByAccount(String account);
    @Select("select count(*) from ftos.user where ID_card = #{ID_card} and ID_type = #{ID_type}")
    int isExistByIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card);
    @Select("select count(*) from ftos.user where u_phone = #{phone}")
    int isExistByPhone(String phone);
    @Select("select count(*) from ftos.user where email = #{email}")
    int isExistByEmail(String email);
    @Update("update ftos.user set u_name = #{name},sex = #{sex},u_account = #{account},u_password = #{password},u_phone = #{phone},city = #{city} where ID_card=#{ID_card}")
    int updatePassenger(@Param("name") String name, @Param("sex") String sex, @Param("ID_card") String ID_card, @Param("account") String account, @Param("password") String password, @Param("phone") String phone,@Param("city") String city);

    //修改个人信息
    @Update("update ftos.user set u_password = #{password} where u_account = #{account}")
    int updatePassword(@Param("password") String password, @Param("account") String account);

    //注销
    @Delete("delete from ftos.user where u_id = #{u_id}")
    int deleteUser(int u_id);
    @Select("select * from ftos.ticket where order_id = #{o_id}")
    List<Ticket> getTicketListByOrderId(int o_id);
    @Update("update ftos.flight_status set economy_space = economy_space + 1 where f_id = #{f_id}")
    int addEconomySpace(int f_id);
    @Update("update ftos.flight_status set business_space = business_space + 1 where f_id = #{f_id}")
    int addBusinessSpace(int f_id);
    @Update("update ftos.flight_status set first_space = first_space + 1 where f_id = #{f_id}")
    int addFirstSpace(int f_id);
    @Delete("delete from ftos.ticket where id = #{t_id}")
    int deleteTicket(int t_id);
    @Delete("delete from ftos.`order` where id = #{o_id}")
    int deleteOrderById(int o_id);
    @Delete("delete from ftos.`order` where userId = #{u_id}")
    int deleteOrder(int u_id);
    @Update("update ftos.bank set money = money + #{money} where ID_type = #{ID_type} and u_ID_card = #{ID_card}")
    int addBankMoneyByUserIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card, @Param("money") double money);
    @Select("select * from ftos.ticket where passenger_id = #{p_id}")
    List<Ticket> getTicketListByPassengerId(int passenger_id);
    @Select("select * from ftos.`order` where id = #{o_id}")
    Order getOrderById(int o_id);
    @Select("select * from ftos.user where u_id = #{u_id}")
    User getUserById(int u_id);
    @Update("update ftos.`order` set price = price - #{price}, passengers = passengers - 1 where id = #{o_id}")
    int updateOrderPriceAndPassengerNum(@Param("o_id") int o_id, @Param("price") double price);
    @Delete("delete from ftos.passenger where passenger_id = #{passenger_id}")
    int deletePassenger(int passenger_id);


    //订单
    @Select("SELECT * FROM ftos.`order` WHERE userId = #{userId} ORDER BY id DESC")
    List<Order> getOrderListByUserId(int userId);
    @Select("select count(*) from ftos.`order` where userId = #{userId}")
    int getOrderNumByUserId(int userId);
    @Select("select count(*) from ftos.`order` where status = 'unpaid' and userId = #{userId}")
    int getUnpaidOrderNum(int userId);
    @Select("select count(*) from ftos.`order` where status = 'paid' and userId = #{userId}")
    int getPaidOrderNum(int userId);
    @Select("select count(*) from ftos.`order` where status = 'completed' and userId = #{userId}")
    int getCompletedOrderNum(int userId);
    @Select("select count(*) from ftos.`order` where status = 'cancelled' and userId = #{userId}")
    int getCancelledOrderNum(int userId);
    @Select("select count(*) from ftos.`order` where status = 'refund' and userId = #{userId}")
    int getRefundOrderNum(int userId);

    //机票
    @Select("SELECT " +
            "t.id, t.order_id, t.passenger_id, t.passenger_name, t.passenger_type, t.flightNo, t.date, t.price, t.seat_type, t.seat, t.status, t.statusText, " +
            "f.departure_airport, f.departure_city, f.departure_date, f.departure_day, f.departure_time, f.arrival_airport, f.arrival_city, f.arrival_date, f.arrival_day, f.arrival_time, f.baggage_limit, f.meal, f.meal_money, f.gate, " +
            "p.plane_id, p.p_model, p.p_airline, p.plane_age, p.WiFi, " +
            "o.paymentDeadline, o.refundDate " +
            "FROM ftos.ticket t " +
            "JOIN ftos.flight f ON t.flightNo = f.f_id " +
            "JOIN ftos.plane p ON f.plane_id = p.plane_id " +
            "JOIN ftos.`order` o ON t.order_id = o.id " +
            "WHERE passenger_id = #{passenger_id} " +
            "  AND t.statusText != '待支付' " +
            "  AND t.statusText != '待退款' " +
            "ORDER BY " +
            "  CASE t.statusText " +
            "    WHEN '即将起飞' THEN 1 " +
            "    WHEN '计划中' THEN 2 " +
            "    ELSE 3 " +
            "  END, " +
            "t.date DESC")
    List<TicketDetail> getTicketDetailsListByPassengerId(int passenger_id);

    @Select("select count(*) from ftos.ticket where passenger_id = #{passenger_id} and statusText <> '待退款' and statusText <> '待支付'")
    int getTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '即将起飞' and passenger_id = #{passenger_id}")
    int getUpcomingTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '已完成' and passenger_id = #{passenger_id}")
    int getCompletedTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '已取消' and passenger_id = #{passenger_id}")
    int getCancelledTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '计划中' and passenger_id = #{passenger_id}")
    int getPlannedTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '待支付' and passenger_id = #{passenger_id}")
    int getUnpaidTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '待退款' and passenger_id = #{passenger_id}")
    int getRefundTicketNumByPassengerId(int passenger_id);
    @Select("select count(*) from ftos.ticket where statusText = '已延误' and passenger_id = #{passenger_id}")
    int getDelayTicketNumByPassengerId(int passenger_id);

    //预定机票
    @Select("select * from ftos.flight where f_id = #{f_id}")
    Flight getFlightById(int f_id);
    @Select("select count(*) from ftos.ticket where passenger_id = #{p_id} and flightNo = #{f_id} and statusText <> '已取消'")
    int isTicketExist(@Param("p_id") int p_id, @Param("f_id") int f_id);
    @Select("select * from ftos.flight f join ftos.flight_status fs on f.f_id = fs.f_id where f.f_id = #{f_id}")
    FlightBooking getFlightBookingByFid(int f_id);
    @Select("select * from ftos.flight_status where f_id = #{f_id}")
    FlightStatus getFlightStatusById(int f_id);
    @Select("SELECT * FROM ftos.`order` ORDER BY id DESC LIMIT 1")
    int getLastOrderId();
    @Insert("insert into ftos.`order`(id,userId, user_name, flightNo, airline, date, `from`, `to`, departure, arrival, price, passengers, status, statusText, paymentMethod, paymentDate) " +
            "values (#{o_id},#{userId}, #{user_name}, #{flightNo}, #{airline}, #{date}, #{from}, #{to}, #{departure}, #{arrival}, #{price}, #{passengers},#{status}, #{statusText}, #{paymentMethod}, #{paymentDate})")
    int addOrder(@Param("o_id") int o_id,@Param("userId") int userId, @Param("user_name") String user_name, @Param("flightNo") int flightNo, @Param("airline") String airline, @Param("date") String date, @Param("from") String from, @Param("to") String to, @Param("departure") String departure, @Param("arrival") String arrival, @Param("price") double price, @Param("passengers") int passengers, @Param("status") String status, @Param("statusText") String statusText, @Param("paymentMethod") String paymentMethod, @Param("paymentDate") String paymentDate);
    @Select("select economy_price from ftos.flight where f_id = #{f_id}")
    double getEconomyPriceByFid(int f_id);
    @Select("select economy_child_price from ftos.flight where f_id = #{f_id}")
    double getEconomyChildPriceByFid(int f_id);
    @Select("select economy_senior_price from ftos.flight where f_id = #{f_id}")
    double getEconomySeniorPriceByFid(int f_id);
    @Select("select economy_infant_price from ftos.flight where f_id = #{f_id}")
    double getEconomyInfantPriceByFid(int f_id);
    @Select("select business_price from ftos.flight where f_id = #{f_id}")
    double getBusinessPriceByFid(int f_id);
    @Select("select business_child_price from ftos.flight where f_id = #{f_id}")
    double getBusinessChildPriceByFid(int f_id);
    @Select("select business_senior_price from ftos.flight where f_id = #{f_id}")
    double getBusinessSeniorPriceByFid(int f_id);
    @Select("select business_infant_price from ftos.flight where f_id = #{f_id}")
    double getBusinessInfantPriceByFid(int f_id);
    @Select("select first_price from ftos.flight where f_id = #{f_id}")
    double getFirstPriceByFid(int f_id);
    @Select("select first_child_price from ftos.flight where f_id = #{f_id}")
    double getFirstChildPriceByFid(int f_id);
    @Select("select first_senior_price from ftos.flight where f_id = #{f_id}")
    double getFirstSeniorPriceByFid(int f_id);
    @Select("select first_infant_price from ftos.flight where f_id = #{f_id}")
    double getFirstInfantPriceByFid(int f_id);
    @Select("select count(*) from ftos.passenger where ID_type = #{ID_type} and passenger_ID_card = #{passenger_ID_card}")
    int isPassengerExistByIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("passenger_ID_card") String ID_card);
    @Update("update ftos.passenger set order_num = order_num + 1 where ID_type = #{ID_type} and passenger_ID_card = #{ID_card}")
    int updatePassengerOrderNum(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card);
    @Insert("insert into ftos.passenger(passenger_name, passenger_sex, ID_type, passenger_ID_card, birthday, order_num) " +
            "values(#{name}, #{sex}, #{ID_type}, #{ID_card}, #{birthday}, 1)")
    int addPassenger(@Param("name") String name, @Param("sex") String sex, @Param("ID_type") String ID_type, @Param("ID_card") String ID_card, @Param("birthday") String birthday);
    @Update("update ftos.flight_status set economy_space = economy_space - 1 where f_id = #{f_id}")
    int updateEconomySpace(int f_id);
    @Update("update ftos.flight_status set business_space = business_space - 1 where f_id = #{f_id}")
    int updateBusinessSpace(int f_id);
    @Update("update ftos.flight_status set first_space = first_space - 1 where f_id = #{f_id}")
    int updateFirstSpace(int f_id);
    @Select("select passenger_id from ftos.passenger where ID_type = #{ID_type} and passenger_ID_card = #{ID_card}")
    int getPassengerIdByIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card);
    @Insert("insert into ftos.ticket(order_id, passenger_id, passenger_name, passenger_type, flightNo, date, price, seat_type, status, statusText) " +
            "values(#{order_id}, #{passenger_id}, #{passenger_name}, #{passenger_type}, #{flightNo}, #{date}, #{price}, #{seat_type}, #{status}, #{statusText})")
    int addTicket(@Param("order_id") int order_id, @Param("passenger_id") int passenger_id, @Param("passenger_name") String passenger_name, @Param("passenger_type") String passenger_type, @Param("flightNo") int flightNo, @Param("date") String date, @Param("price") double price, @Param("seat_type") String seat_type,@Param("status") String status, @Param("statusText") String statusText);
    @Select("select money from ftos.bank where ID_type = #{ID_type} and u_ID_card = #{ID_card}")
    double getBankMoneyByUserIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card);
    @Update("update ftos.bank set money = money - #{money} where ID_type = #{ID_type} and u_ID_card = #{ID_card}")
    int updateBankMoneyByUserIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card, @Param("money") double money);


    //绑定邮箱
    @Select("select email from ftos.user where u_id = #{u_id}")
    String getEmailByUserId(int u_id);
    @Update("update ftos.user set email = #{email} where u_id = #{u_id}")
    int updateEmailByUserId(@Param("u_id") int u_id, @Param("email") String email);
}